<?php
class AdminCounts
{
	private $db;
	private $tableUser;
	private $tableLogin;
	private $tablePay;
	
	public function __construct()
	{
		$this->db=new Mysql('love');
		$this->tableUser = $this->db->pre.'userinfo';
		$this->tableLogin = $this->db->pre.'log_login';
		$this->tablePay = $this->db->pre.'qz_buy_goods_billinfo' ;
		
	}
	
	public function getReg()
	{
		$where='';
		if (!Http::get('c_sregist_time')){
			$_GET['c_sregist_time'] = date("Y-m-d",strtotime("-2 days"));	
		}
		$where = $where." AND c_regist_time >= '".Http::get('c_sregist_time').' 00:00:00'."'";
		
		if (!Http::get('c_eregist_time')){
			$_GET['c_eregist_time'] = date("Y-m-d");	
		}
		$where = $where." AND c_regist_time <= '".Http::get('c_eregist_time').' 23:59:59'."'";
		
		$sql = "SELECT  LEFT(c_regist_time,10) AS regdate,COUNT(c_regist_time) AS count_new_regist   FROM ".$this->tableUser." WHERE  1 ".$where;
		$sql = $sql." GROUP BY  regdate ORDER BY c_regist_time DESC";
//echo $sql;
		$rs = $this->db->getAll($sql);
		return $rs;
	}
	
	public function getOnlineuser()
	{
		$where='';
		if (!Http::get('login_sdatetime')){
			$_GET['login_sdatetime'] = date("Y-m-d",strtotime("-10 days"));	
		}
		
		if (!Http::get('login_edatetime')){
			$_GET['login_edatetime'] = date("Y-m-d");	
		}
		
		$st = strtotime(Http::get('login_sdatetime'));
		$et = strtotime(Http::get('login_edatetime'));
		$sql = "SELECT  max(c_count_man+c_count_woman) as count_user ,left(c_time,10) as timeline FROM t_log_count_context group by left(c_time,10) order by c_time desc ";
		return $this->db->getAll($sql);
	}
	
	public function getAlluserlog()
	{
		$where= '';
		if (!Http::get('c_slogin_datetime')){
			$_GET['c_slogin_datetime'] = date("Y-m-d",strtotime("-2 days"));	
		}
		$where = $where." AND c_login_datetime >= '".Http::get('c_slogin_datetime').' 00:00:00'."'";
		
		if (!Http::get('c_elogin_datetime')){
			$_GET['c_elogin_datetime'] = date("Y-m-d");	
		}
		$where = $where." AND c_login_datetime <= '".Http::get('c_elogin_datetime').' 23:59:59'."'";
		
		$sql = "SELECT  
		LEFT(c_login_datetime,10) AS login_datetime,
		COUNT(c_login_datetime) as count_login_datetime ,
		COUNT(DISTINCT c_userid)  as count_login_users,
		SUM(c_interval) AS sum_online_time
		
		FROM `".$this->tableLogin."` WHERE 1 ".$where.
		 " GROUP BY  login_datetime  ORDER BY c_login_datetime DESC";
		//echo $sql;
		$rs = $this->db->getAll($sql);
		
		return $rs;
	}
	
	public function getOlduserlog()
	{
		$where = '';
		if (!Http::get('c_slogin_time')){
			$_GET['c_slogin_time'] = date("Y-m-d",strtotime("-2 days"));	
		}
		$where = $where." AND c_login_time >= '".Http::get('c_slogin_time').' 00:00:00'."'";
		
		if (!Http::get('c_elogin_time')){
			$_GET['c_elogin_time'] = date("Y-m-d");	
		}
		$where = $where." AND c_login_time <= '".Http::get('c_elogin_time').' 23:59:59'."'";

		$sql = "SELECT  LEFT(c_login_time,10) AS regdate,COUNT(DISTINCT c_id)  as count_oldlogin_users  
		  FROM ".$this->tableUser." WHERE UNIX_TIMESTAMP(c_login_time)-UNIX_TIMESTAMP(c_regist_time)>604800 ".$where;
		$sql = $sql." GROUP BY  regdate  ORDER BY c_login_time DESC";		
		$rs = $this->db->getAll($sql);
		return $rs;
	}
	
	public function getPay()
	{
		$where = '';
		if (!Http::get('c_sdatetime')){
			$_GET['c_sdatetime'] = date("Y-m-d",strtotime("-1 days"));	
		}
		$where = $where." AND c_datetime >= '".Http::get('c_sdatetime').' 00:00:00'."'";
		
		if (!Http::get('c_edatetime')){
			$_GET['c_edatetime'] = date("Y-m-d");	
		}
		$where = $where." AND c_datetime <= '".Http::get('c_edatetime').' 23:59:59'."'";
		
		$sql = "SELECT  
		LEFT(c_datetime,10)  AS dateline ,
		sum(c_amt) as totalPayMoney,
		sum(if(c_amt>1 ,1,0)) as totalPayTimes,
		count(DISTINCT c_id ) as totalPayUser
		
		 FROM ".$this->tablePay."  WHERE c_amt>0 ".$where." GROUP BY dateline desc  ";
		// echo $sql;
		$rs = $this->db->getAll($sql);		
	
		
		
		$where = '';
		$where = $where." AND c_login_datetime >= '".Http::get('c_sdatetime').' 00:00:00'."'";
		$where = $where." AND c_login_datetime <= '".Http::get('c_edatetime').' 23:59:59'."'";
		$sql = "SELECT  
		LEFT(c_login_datetime,10) AS login_datetime,		
		COUNT(DISTINCT c_userid)  as count_login_users			
		FROM `".$this->tableLogin."` WHERE 1 ".$where.
		 " GROUP BY  login_datetime  ORDER BY c_login_datetime DESC";
		//echo $sql;
		$rs2 = $this->db->getAll($sql);
		foreach ($rs as $key=>$value){
			foreach ($rs2 as $k=>$v){
				if ($value['dateline']==$v['login_datetime']) $rs[$key]['totalLoginuser'] = $v['count_login_users'];
			}
		}
		
		return $rs; 
		
		
	}
	
	//2011-01
	public function getActuser()
	{
		$where = '';
		if (!Http::get('c_sdatetime')){
			$_GET['c_sdatetime'] = date("Y-m");
			$endDay = date("t");
		}else {
			$endDay = date("t",strtotime(Http::get('c_sdatetime')));
		}
		
		$where = $where." AND c_login_datetime >= '".Http::get('c_sdatetime')."-01 00:00:00'";	
		$where = $where." AND c_login_datetime <= '".Http::get('c_sdatetime')."-".$endDay." 23:59:59'";	
		
		$sql = " SELECT 		
		COUNT(c_userid ) AS count_login_times		
		FROM ".$this->tableLogin." WHERE 1 ".$where." GROUP BY c_userid HAVING count_login_times >3";
		$this->db->query($sql);		
		$totalActUser = $this->db->query($sql);
		$totalActUser = $this->db->affected_rows();
		$rs = array();
		$rs['actUser'] = $totalActUser;	
		$where1 = '';
		$where1 = $where1." AND c_regist_time>= '".Http::get('c_sdatetime')."-01 00:00:00'";	
		$where1 = $where1." AND c_regist_time<= '".Http::get('c_sdatetime')."-".$endDay." 23:59:59'";	
		$sql = "SELECT COUNT(1) FROM t_userinfo WHERE 1 ".$where1;
		$totalRegUser = $this->db->getOne($sql);
		$rs['regUser'] = $totalRegUser;	
		return $rs;
		
	}
	public function getPerrefill()
	{
		$where = '';
		if (!Http::get('c_sdatetime')){
			$_GET['c_sdatetime'] = date("Y-m");
			$endDay = date("t");
		}else {
			$endDay = date("t",strtotime(Http::get('c_sdatetime')));
		}
		
		$where = $where." AND c_datetime_begin >= '".Http::get('c_sdatetime')."-01 00:00:00'";	
		$where = $where." AND c_datetime_begin <= '".Http::get('c_sdatetime')."-".$endDay." 23:59:59'";	
		$sql = "SELECT  
		COUNT(1) AS sume,
		sum(if(c_addtime,1,0)) as sumre
		FROM t_log_barchat_talking_record  WHERE 1 ".$where;
		$rs = $this->db->getRow($sql);		
		return $rs; 
		
	}
	public function getSells()
	{
		$where = '';
		if (!Http::get('c_sdatetime')){
			$_GET['c_sdatetime'] = date("Y-m-d",strtotime("-1 days"));	
		}
		$where = $where." AND c_action_datetime >= '".Http::get('c_sdatetime').' 00:00:00'."'";
		
		if (!Http::get('c_edatetime')){
			$_GET['c_edatetime'] = date("Y-m-d");	
		}
		$where = $where." AND c_action_datetime <= '".Http::get('c_edatetime').' 23:59:59'."'";
		
		$sql = "SELECT  
		LEFT(c_action_datetime,10)  AS dateline ,
		
		sum(if(c_beautiful_type in(101,102,103,104,105)  ,c_beautiful_gold,0)) as totalClothesPayGold,
		sum(if(c_beautiful_type in(101,102,103,104,105)  ,c_beautiful_money,0)) as totalClothesPayMoney,
		
		sum(if(c_beautiful_type in(106,107,108,109,110,111,112,113,114,115,116)  ,c_beautiful_gold,0)) as totalAccessoriesPayGold,
		sum(if(c_beautiful_type in(106,107,108,109,110,111,112,113,114,115,116)  ,c_beautiful_money,0)) as totalAccessoriesPayMoney,
		
		sum(if(c_beautiful_type in(201,202,203,204,205,206,207)  ,c_beautiful_gold,0)) as totalGiftPayGold,
		sum(if(c_beautiful_type in(201,202,203,204,205,206,207)  ,c_beautiful_money,0)) as totalGiftPayMoney,
		
		sum(if(c_beautiful_type in(301,302,303,304,305,306)  ,c_beautiful_gold,0)) as totalFurniturePayGold,
		sum(if(c_beautiful_type in(301,302,303,304,305,306)  ,c_beautiful_money,0)) as totalFurniturePayMoney
		
		FROM t_log_buy_beautiful  WHERE 1 AND (c_beautiful_id not in(1052,1048,1026,1085) or c_beautiful_type not in (101,102)) ".$where." GROUP BY dateline desc  ";
		// echo $sql;
		$rs = $this->db->getAll($sql);		
		return $rs; 
	}
	public function getEncounter()
	{
		//t_log_barchat_talking_record
		$where = '';
		if (!Http::get('c_sdatetime')){
			$_GET['c_sdatetime'] = date("Y-m-d",strtotime("-1 days"));	
		}
		$where = $where." AND c_datetime_begin >= '".Http::get('c_sdatetime').' 00:00:00'."'";
		
		if (!Http::get('c_edatetime')){
			$_GET['c_edatetime'] = date("Y-m-d");	
		}
		$where = $where." AND c_datetime_begin <= '".Http::get('c_edatetime').' 23:59:59'."'";
		
		$sql = "SELECT  
		LEFT(c_datetime_begin,10)  AS dateline ,
		COUNT(c_user_id) AS count_user,
		COUNT(DISTINCT c_user_id) as count_e_user 		
		FROM t_log_barchat_talking_record WHERE 1 ".$where." GROUP BY dateline desc  ";
		// echo $sql;
		$rs = $this->db->getAll($sql);		
		return $rs; 
	}
	
	public function getPermf(){
		$where = '';
		
		
		$sql = "SELECT  
		
		SUM(if(c_basic_gender = 1 ,1,0)) as summ,
		SUM(if(c_basic_gender = 0 ,1,0)) as sumfm
		FROM t_userinfo WHERE 1 ".$where."   ";
		// echo $sql;
		$rs = $this->db->getAll($sql);		
		return $rs; 
	}
	public function getAges()
	{
		$where = '';
		$sql = "SELECT 	
		LEFT (c_basic_birthday,3) as timeline,
		COUNT(1) as sumuser	,
		SUM(if(c_basic_gender = 1 ,1,0)) as summ,
		SUM(if(c_basic_gender = 0 ,1,0)) as sumfm	
		FROM t_userinfo WHERE 1 ".$where."  GROUP BY timeline DESC ";
		// echo $sql;
		$rs = $this->db->getAll($sql);		
		return $rs; 
	}
	
	public function getAreas()
	{
		$where = '';
		$sql = "SELECT 	
		c_basic_address_province,
		SUM(if(c_basic_gender = 1 ,1,0)) as summ,
		SUM(if(c_basic_gender = 0 ,1,0)) as sumfm,
		COUNT(1) as sumuser	
		FROM t_userinfo WHERE 1 ".$where."  GROUP BY c_basic_address_province DESC ";
		// echo $sql;
		$rs = $this->db->getAll($sql);		
		return $rs;
	}
	public function getMonthleft()
	{
		if (!Http::get('c_sdatetime')){
			$_GET['c_sdatetime'] = date("Y-m",strtotime("-1 months"));				
		}
		
		if (!Http::get('c_edatetime')){			
			$_GET['c_edatetime'] = date("Y-m");			
		}
		
		$lastRegSTime = Http::get('c_sdatetime').'-01 00:00:00';
		$lastRegETime = Http::get('c_sdatetime').'-31 23:59:59';
		
		$thisMonthLoginStime = Http::get('c_edatetime').'-01 00:00:00';
		$thisMonthLoginEtime = Http::get('c_edatetime').'-31 23:59:59';
		$rs = array();
		$sql = "SELECT COUNT(1) FROM t_userinfo b WHERE b.c_regist_time >='$lastRegSTime' AND b.c_regist_time<='$lastRegETime' ";
		$rs['sumregnums'] = $this->db->getOne($sql);
		$sql = "SELECT 
		count(distinct a.c_userid ) as sumnums,		
		count(1) as sumlognums
		FROM t_log_login a LEFT JOIN t_userinfo b ON a.c_userid = b.c_id 
		WHERE b.c_regist_time >='$lastRegSTime' AND b.c_regist_time<='$lastRegETime' ";		
		$sql = $sql." AND a.c_login_datetime >='$thisMonthLoginStime' AND a.c_login_datetime<='$thisMonthLoginEtime' ";
		//echo $sql;
		$row = $this->db->getRow($sql);
		$sql = "SELECT 				
		count(1) as sumlognums
		FROM t_log_login a LEFT JOIN t_userinfo b ON a.c_userid = b.c_id 
		WHERE b.c_regist_time >='$lastRegSTime' AND b.c_regist_time<='$lastRegETime' ";		
		$sql = $sql." AND a.c_login_datetime >='$thisMonthLoginStime' AND a.c_login_datetime<='$thisMonthLoginEtime' group by a.c_userid having count(a.c_userid)>2";
		
		$row2 = $this->db->query($sql);
		$rs['sumlogintimes'] = $row['sumnums'];//登录人数
		$rs['sumlognums'] = $row['sumlognums'];//登录次数
		$rs['sumlog3nums'] = $this->db->affected_rows();//登录3次以上人数
		return $rs;
	}
	public function getWeekleft()
	{
		if (!Http::get('c_sdatetime')){
			
			if(date("w")>1 or date("w")==0){
				$lastMonay = date("Y-m-d",strtotime("last Monday")-(7*24*60*60));
			}else {
				$lastMonay = date("Y-m-d",strtotime("last Monday"));
			}
			
			$_GET['c_sdatetime'] =$lastMonay;
					
		}
		
	
		if (!Http::get('c_edatetime')){	
			$lastSunday = date("Y-m-d",strtotime("last Sunday"));		
			$_GET['c_edatetime'] = $lastSunday;			
		}
		
		if (!Http::get('c_s1datetime')){
			
			if(date("w")>1 or date("w")==0){
				$lastMonay = date("Y-m-d",strtotime("this Monday")-(7*24*60*60));
			}else {
				$lastMonay = date("Y-m-d",strtotime("this Monday"));
			}
			
			$_GET['c_s1datetime'] =$lastMonay;
					
		}
		
	
		if (!Http::get('c_e1datetime')){	
			$lastSunday = date("Y-m-d",strtotime("this Sunday"));		
			$_GET['c_e1datetime'] = $lastSunday;			
		}
		
		
		
		$lastRegSTime = Http::get('c_sdatetime').' 00:00:00';
		$lastRegETime = Http::get('c_edatetime').' 23:59:59';
		
		$thisMonthLoginStime = Http::get('c_s1datetime').' 00:00:00';
		$thisMonthLoginEtime = Http::get('c_e1datetime').' 23:59:59';
		$rs = array();
		$sql = "SELECT COUNT(1) FROM t_userinfo b WHERE b.c_regist_time >='$lastRegSTime' AND b.c_regist_time<='$lastRegETime' ";
		$rs['sumregnums'] = $this->db->getOne($sql);
		$sql = "SELECT 
		count(distinct a.c_userid ) as sumnums,		
		count(1) as sumlognums
		FROM t_log_login a LEFT JOIN t_userinfo b ON a.c_userid = b.c_id 
		WHERE b.c_regist_time >='$lastRegSTime' AND b.c_regist_time<='$lastRegETime' ";		
		$sql = $sql." AND a.c_login_datetime >='$thisMonthLoginStime' AND a.c_login_datetime<='$thisMonthLoginEtime' ";
		//echo $sql;
		$row = $this->db->getRow($sql);
		$sql = "SELECT 				
		count(1) as sumlognums
		FROM t_log_login a LEFT JOIN t_userinfo b ON a.c_userid = b.c_id 
		WHERE b.c_regist_time >='$lastRegSTime' AND b.c_regist_time<='$lastRegETime' ";		
		$sql = $sql." AND a.c_login_datetime >='$thisMonthLoginStime' AND a.c_login_datetime<='$thisMonthLoginEtime' group by a.c_userid having count(a.c_userid)>2";
		
		$row2 = $this->db->query($sql);
		$rs['sumlogintimes'] = $row['sumnums'];//登录人数
		$rs['sumlognums'] = $row['sumlognums'];//登录次数
		$rs['sumlog3nums'] = $this->db->affected_rows();//登录3次以上人数
		return $rs;
	}
	
	public function getPerpay()
	{
		if (!Http::get('c_sdatetime')){
			$_GET['c_sdatetime'] = date("Y-m").'-01';				
		}
		
		if (!Http::get('c_edatetime')){			
			$_GET['c_edatetime'] = date('Y-m-t');			
		}
		
	
		
		$thisMonthLoginStime = Http::get('c_sdatetime').' 00:00:00';
		$thisMonthLoginEtime = Http::get('c_edatetime').' 23:59:59';
		
		//COUNT(DISTINCT c_userid) as count_user  FROM `".$this->tableLogin."` 
					//WHERE 1 AND  c_login_datetime >= '".date("Y-m-d",strtotime("+ $i days",$st))." 00:00:00' AND c_login_datetime
		$sql = "SELECT COUNT(DISTINCT c_userid) FROM ".$this->tableLogin." WHERE c_login_datetime>='$thisMonthLoginStime' AND c_login_datetime<='$thisMonthLoginEtime' ";
		$loginCount = $this->db->getOne($sql);
		$sql = "CREATE temporary table t00010 SELECT c_user_id,
		SUM( c_user_id) AS payUsers ,
		SUM( IF(c_beautiful_gold>0,1,0)) AS payGoldUsers,
		SUM( IF(c_beautiful_money>0,1,0)) AS payMoneyUsers,
				
		SUM(c_beautiful_gold) as payGlod ,
		SUM(c_beautiful_money) as payMoney 
		FROM t_log_buy_beautiful WHERE c_beautiful_gold>0 or c_beautiful_money>0 and c_action_datetime>='$thisMonthLoginStime' AND c_action_datetime<='$thisMonthLoginEtime' 
		GROUP BY c_user_id
		";
		//echo $sql;
		
		$this->db->query($sql);
		$sql = "SELECT  
		count( c_user_id) AS payUsers ,
		SUM( IF(payGoldUsers>0,1,0)) AS payGoldUsers,
		SUM( IF(payMoneyUsers>0,1,0)) AS payMoneyUsers,				
		SUM(payGlod) as payGlod ,
		SUM(payMoney) as payMoney 
		FROM t00010    ";
		$row = $this->db->getRow($sql);
		$row['loginCount'] = $loginCount;
		return $row;
	}
	/**
	 * 
	 * 周活跃度  平均每个账号每周登录的次数 = 每周总登录数/每周登录人数
	 */
	public function getPeract()
	{
		if (!Http::get('c_sdatetime')){
			
			if(date("w")>1 or date("w")==0){
				$lastMonay = date("Y-m-d",strtotime("last Monday")-(7*24*60*60));
			}else {
				$lastMonay = date("Y-m-d",strtotime("last Monday"));
			}
			
			$_GET['c_sdatetime'] =$lastMonay;
					
		}
		
	
		if (!Http::get('c_edatetime')){	
			$lastSunday = date("Y-m-d",strtotime("last Sunday"));		
			$_GET['c_edatetime'] = $lastSunday;			
		}
		$thisLoginStime = Http::get('c_sdatetime').' 00:00:00';
		$thisLoginEtime = Http::get('c_edatetime').' 23:59:59';
		$sql = "SELECT COUNT(DISTINCT c_userid) as countUsers ,COUNT(1) as countTimes  FROM ".$this->tableLogin
		." WHERE c_login_datetime>='$thisLoginStime' AND c_login_datetime<='$thisLoginEtime' ";
		return $this->db->getRow($sql);
	}
	public function getPerphoto()
	{
		$sql = "SELECT 
		COUNT(1) AS countUsers ,
		SUM(IF(c_admit_head_img='0',1,0)) AS countPusers ,
		SUM(IF(c_admit_head_img='0' and c_basic_gender=1,1,0)) AS countPmusers ,
		SUM(IF(c_admit_head_img='0' and c_basic_gender=0,1,0)) AS countPfusers 
		
		FROM ".$this->tableUser ." WHERE 1 " ;
		return $this->db->getRow($sql);
	}
}


